package es.ats.codington.festival.daos;

import java.io.IOException;

import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.mysql.jdbc.Statement;


import es.ats.codington.festival.Mapper.PeopleMapper;
import es.ats.codington.festival.Mapper.VisitorMapper;
import es.ats.codington.festival.domain.Visitor;

import es.ats.codington.festival.interfaces.daos.iVisitorDAO;


/**
 * The Class VisitorDAOImpl.
 */
public class VisitorDAOImpl implements iVisitorDAO {
	
	
	/** The jdbc template object. */
	private JdbcTemplate jdbcTemplateObject;
	
	/** The query properties. */
	private Properties queryProperties;

	/**
	 * Instantiates a new visitor dao impl.
	 *
	 * @param jdbcTemplateObject the jdbc template object
	 * @param queryProperties the query properties
	 */
	public VisitorDAOImpl(JdbcTemplate jdbcTemplateObject,Properties queryProperties ) {
		this.jdbcTemplateObject = jdbcTemplateObject;
		this.queryProperties = queryProperties;
	} 
	
	/**
	 * Instantiates a new visitor dao impl.
	 */
	public VisitorDAOImpl() {
	}

	
	/**
	 * Insert visitor.
	 *
	 * @param visitante1 the visitante1
	 * @return true, if successful
	 * @throws Exception the exception
	 */
@Override
	public boolean insertVisitor(final Visitor visitante1) throws  Exception {
		int affectedRows=0; int affectedRows2=0;
		KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
		
		
			List<Visitor> otherVisitor =jdbcTemplateObject.query(queryProperties.getProperty("selec_username"),new PreparedStatementSetter(){
				@Override
				public void setValues(PreparedStatement statement)throws SQLException{
				}
			
			},new VisitorMapper());
			
			int size = otherVisitor.size();
			int iter;
			for (iter=0; iter<size; iter++){
				if(visitante1.getUsername().equals(otherVisitor.get(iter).getUsername())){
					throw new Exception("This Username is not available");
				}
			}
			
			affectedRows = jdbcTemplateObject.update(new PreparedStatementCreator(){
				
				@Override
				public PreparedStatement createPreparedStatement(Connection connection) throws SQLException{
					
					PreparedStatement pstatement = connection.prepareStatement(queryProperties.getProperty("insert_visitor"),Statement.RETURN_GENERATED_KEYS);
					pstatement.setString(1, visitante1.getName());
					pstatement.setString(2, visitante1.getLastname());
					pstatement.setString(3, visitante1.getMail());
					pstatement.setString(4, visitante1.getAddress());
					pstatement.setInt(5, visitante1.getPhone());
					pstatement.setString(6, visitante1.getDni());	
					
					return pstatement;}
				},generatedKeyHolder);

			
			final int key = generatedKeyHolder.getKey().intValue();

			affectedRows2 = jdbcTemplateObject.update(queryProperties.getProperty("insert_user"),new PreparedStatementSetter(){
				
				@Override
				public void setValues(PreparedStatement statement)throws SQLException{
					statement.setString(1, visitante1.getUsername());
					statement.setString(2, visitante1.getPassword());
					statement.setInt(3, key);	
				}
			});
			
			if(affectedRows!=0){
				return true;
			}else{
				return false;
			}
		
	}
	
	
	/**
	 * Login visitor.
	 *
	 * @param visitante2 the visitante2
	 * @return the int
	 * @throws Exception the exception
	 */
	@Override 
	public int loginVisitor(final Visitor visitante2) throws Exception{
		int idvisitor=0;	
		
		List<Visitor> visitors = jdbcTemplateObject.query(queryProperties.getProperty("selec_visitor"),new PreparedStatementSetter(){
			
		@Override
		public void setValues(PreparedStatement statement)throws SQLException{
			statement.setString(1,visitante2.getUsername());
			statement.setString(2,visitante2.getPassword());
			}
		}, new VisitorMapper());
		
		
		if(visitors.size()==0){
			throw new Exception("Username or password incorrect");
			
		}else{	
			Visitor vis = visitors.get(0);
			idvisitor = vis.getiDvisitor();
			return idvisitor;
		}
	}

				

	
	/**
	 * Update visitor.
	 *
	 * @param visitante3 the visitante3
	 * @param idvisitor the idvisitor
	 * @return true, if successful
	 * @throws Exception the exception
	 */
	@Override
	public boolean updateVisitor(final Visitor visitante3, final int idvisitor) throws Exception{
		
		int affectedRows=0;
				
		
			List<Visitor> visitors = jdbcTemplateObject.query(queryProperties.getProperty("selec_visitor"),new PreparedStatementSetter(){
			
				@Override
				public void setValues(PreparedStatement statement)throws SQLException{
					statement.setString(1,visitante3.getUsername());
					statement.setString(2,visitante3.getPassword());
					}
				}, new VisitorMapper());
			
			int size = visitors.size();
			if (size==0){
				throw new Exception("Username or Password incorrect");
			}else{
				if(visitors.get(0).getiDvisitor()==idvisitor){
				}else{
					throw new Exception("Username or Password incorrect");
				}
			}
				
				
		
			affectedRows= jdbcTemplateObject.update(queryProperties.getProperty("update_visitor"),new PreparedStatementSetter(){	
				
				@Override
				public void setValues(PreparedStatement statement)throws SQLException{
				statement.setString(1, visitante3.getName());
				statement.setString(2, visitante3.getLastname());
				statement.setString(3, visitante3.getMail());
				statement.setString(4, visitante3.getAddress());
				statement.setLong(5, visitante3.getPhone());
				statement.setString(6, visitante3.getDni());
				statement.setInt(7, idvisitor);
				}
			});
				
			if(affectedRows!=0){
				return true;
			}
			else{
				return false;
			}
			
		
		
		}
		

	/* (non-Javadoc)
	 * @see es.ats.codington.festival.interfaces.daos.iVisitorDAO#updatePass(es.ats.codington.festival.domain.Visitor, java.lang.String, int)
	 */
	@Override
	public boolean updatePass(final Visitor visitante4, final String newpassword, final int idvisitor) throws Exception {

		int affectedRows = 0;		

		
			List<Visitor> visitors = jdbcTemplateObject.query(queryProperties.getProperty("selec_visitor"),new PreparedStatementSetter(){	
				
				public void setValues(PreparedStatement statement)throws SQLException{
					statement.setString(1,visitante4.getUsername());
					statement.setString(2,visitante4.getPassword());
					}
				}, new VisitorMapper());
				
			int size = visitors.size();
			if (size==0){
				throw new Exception("Username or Password incorrect");
			}else{
				if(visitors.get(0).getiDvisitor()==idvisitor){
				}else{
					throw new Exception("Username or Password incorrect");
				}
			}
			
			affectedRows= jdbcTemplateObject.update(queryProperties.getProperty("update_pass"),new PreparedStatementSetter(){	
			
			@Override
			public void setValues(PreparedStatement statement)throws SQLException{
				statement.setString(1, newpassword);
				statement.setInt(2, idvisitor);

			}});
				
				
		if(affectedRows!=0){
			return true;
		}
		else{
			return false;
		}
		
	}
	
}